package service;

import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import util.OrderNo;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.sun.org.apache.xerces.internal.impl.dv.xs.DayDV;

import db.DBConn;
import entities.CartEntity;
import entities.OrderEntity;
import entities.CartEntity;
import entities.UserEntity;

/*
 * 说明：这个和android客户端有不同，android客户端购物车数据时保存在手机本地数据库，
 * 而BS部分可以直接将购物车中的数据保存到订单表，默认状态为0即可，
 * 
 * 
 * */
public class TOrderService {
	// 保存订单信息
	public static String saveOrder(String userName, String toAddr,
			String toUser, String toTime, String isPaid,String randomNum) throws SQLException {
		// 生成订单号
		String orderNo = "";
		try {
			orderNo = "T" + OrderNo.getOdn();
			System.out.println("订单号" + orderNo);
		} catch (Exception e) {
			e.printStackTrace();
			return "failed";
		}

		System.out.println(userName);

		UserEntity user = new UserEntity();
		user = UserService.getUser(userName);
		if (user.getId() == 0) {
			return "failed";
		}

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		try {
			// 更改JDBC事务的默认提交方式
			conn.setAutoCommit(false);
			// 查询购物车中的数据
			List<CartEntity> cartList = new ArrayList<CartEntity>();
			cartList = TCartService.selectAll(userName);
			if (cartList.size() == 0) {
				System.out.println("你的购物车为空呢");
				return "failed";
			}

			String orderSql = toOrderString(cartList.get(0).getSellerId(), user.getId(), cartList, orderNo,
					toAddr, toUser, toTime, isPaid,randomNum);
			stmt.execute(orderSql);

			// 成功后修改订单项的订单状态与支付状态
			String updateSql = "	update shop_orderitem set item_state =1 ,item_isPaid = 0 , item_orderNo ='"
					+ orderNo
					+ "'  where item_userId = "
					+ user.getId()
					+ " and item_state=0 and item_orderNo like 't_wait_a_minute'";
			if ("true".equals(isPaid)) {
				updateSql = "update shop_orderitem set item_state = 2 ,item_isPaid = 1 , item_orderNo ='"
						+ orderNo
						+ "'  where item_userId = "
						+ user.getId()
						+ " and item_state=0 and item_orderNo like 't_wait_a_minute'";
			}

			stmt.executeUpdate(updateSql);

			// 提交JDBC事务
			conn.commit();
			// 恢复JDBC事务的默认提交方式
			conn.setAutoCommit(true);
			stmt.close();
			conn.close();
			// 操作成功，返回订单数据
			return orderNo;
		} catch (Exception ex) {
			// 回滚JDBC事务
			conn.rollback();
			ex.printStackTrace();
			stmt.close();
			conn.close();
			// 数据库操作失败，返回ERROR
			return "failed";
		}
		// }
	}

	// 根据订单项数据生成订单插入语句
	private static String toOrderString(Integer sellerId, int userId,
			List<CartEntity> cartList, String orderNo, String toAddr,
			String toUser, String toTime, String isPaid,String randomNum) throws SQLException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = sdf.format(new java.util.Date());
		String payState = "1";
		if ("true".equals(isPaid)) {
			payState = "2";
		}
		//订单类型
		String type = " 1 , '"+randomNum+"' ";
		if("".equals(randomNum)){
			type = " 0 , null ";
		}

		String sql = "insert into shop_order values( null ,'" + userId + "','"
				+ orderNo + "','" + sellerId + "','" + toOrder(cartList)
				+ "','" + toAddr + "','" + time + "','" + toUser + "','"
				+ toTime + "','" + payState + "' , "+type+"  );";
		System.out.println("sql is---->" + sql);
		return sql;
	}

	// 根据订单项列表生成订单描述
	private static String toOrder(List<CartEntity> cartList)
			throws SQLException {
		String orderInfo = "";
		for (CartEntity cart : cartList) {
			orderInfo += GoodsService.getPointGoods(cart.getGoodsId())
					.getName() + "[" + cart.getItemCount() + " 份],";
		}
		return orderInfo.substring(0, orderInfo.length() - 1);
	}

	/*// 查询订单数据(针对用户)
	// 当用户需要从服务器端查询自己的 购物历史时
	public static List<OrderEntity> getUserOrder(String userName, String start,
			String count) throws SQLException {
		List<OrderEntity> orderList = new ArrayList<OrderEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);

		String sql = "select * from shop_order where  order_userId = "
				+ user.getId()
				+ "  group by order_no  order by order_addtime desc limit "
				+ start + "," + count;

		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			OrderEntity order = new OrderEntity();
			order.setId(rs.getInt("order_id"));
			order.setDetail(rs.getString("order_detail"));
			order.setNo(rs.getString("order_no"));
			order.setSellerId(rs.getInt("order_sellerId"));
			order.setTime(sdf.format(rs.getTimestamp("order_addTime")));
			order.setToAddr(rs.getString("order_state"));
			order.setToAddr(rs.getString("order_deliveryAddr"));
			order.setToUser(rs.getString("order_deliveryUser"));
			order.setToTime(rs.getString("order_deliveryTime"));
			order.setState(rs.getInt("order_state"));
			order.setCartList(CartService.selectRecent(userName,
					rs.getString("order_no")));
			orderList.add(order);
		}
		rs.close();
		stmt.close();
		conn.close();
		return orderList;
	}

	// 查询刚提交的订单数据
	// 当用户需要从服务器端查询自己的 购物历史时
	public static List<OrderEntity> getOrder(String userName, String orderNo)
			throws SQLException {
		List<OrderEntity> orderList = new ArrayList<OrderEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();

		UserEntity user = new UserEntity();
		user = UserService.getUser(userName);
		if (user.getId() == 0) {
			return null;
		}

		String sql = "select * from shop_order where  order_userId = "
				+ user.getId() + " and order_no like '" + orderNo
				+ "' group by order_no ";

		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			OrderEntity order = new OrderEntity();
			order.setId(rs.getInt("order_id"));
			order.setDetail(rs.getString("order_detail"));
			order.setNo(rs.getString("order_no"));
			order.setSellerId(rs.getInt("order_sellerId"));
			order.setTime(sdf.format(rs.getTimestamp("order_addTime")));
			order.setToAddr(rs.getString("order_state"));
			order.setToAddr(rs.getString("order_deliveryAddr"));
			order.setToUser(rs.getString("order_deliveryUser"));
			order.setToTime(rs.getString("order_deliveryTime"));
			order.setState(rs.getInt("order_state"));
			order.setCartList(CartService.selectRecent(userName, orderNo));
			orderList.add(order);
		}
		rs.close();
		stmt.close();
		conn.close();
		return orderList;

	}

	// 订单统计
	public static String orderCount(String userName, String from, String to)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String result = "";
		// 总共定了几次外卖
		UserEntity user = UserService.getUser(userName);
		int userId = user.getId();

		String countSql = "select * from shop_user, shop_order where shop_user.user_id = shop_order.order_userId and shop_user.user_id = "
				+ userId
				+ " and shop_order.order_addTime between '"
				+ from
				+ "' and '" + to + "'  group by shop_order.order_no";

		ResultSet rs = stmt.executeQuery(countSql);
		List<OrderEntity> orderList = new ArrayList<OrderEntity>();
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			OrderEntity order = new OrderEntity();
			order.setId(rs.getInt("order_id"));
			order.setDetail(rs.getString("order_detail"));
			order.setNo(rs.getString("order_no"));
			order.setSellerId(rs.getInt("order_sellerId"));
			order.setTime(sdf.format(rs.getTimestamp("order_addTime")));
			order.setToAddr(rs.getString("order_state"));
			order.setToAddr(rs.getString("order_deliveryAddr"));
			order.setToUser(rs.getString("order_deliveryUser"));
			order.setToTime(rs.getString("order_deliveryTime"));
			order.setState(rs.getInt("order_state"));
			order.setCartList(CartService.selectRecent(userName,
					rs.getString("order_no")));
			orderList.add(order);
		}
		result += orderList.size() + ",";

		// 平均花费
		double priceAll = 0d;
		for (int i = 0; i < orderList.size(); i++) {
			List<CartEntity> cartList = orderList.get(i).getCartList();
			for (int j = 0; j < cartList.size(); j++) {
				priceAll += cartList.get(j).getGoodsPrice()
						* cartList.get(j).getItemCount();
			}
		}
		result += String.format("%.2f,%.1f", priceAll,
				priceAll / orderList.size())
				+ ",";

		// 最喜欢的商家及去的次数
		String hotSeller = "select seller_name, count(*) as count from shop_order,shop_seller where seller_id = order_sellerId and order_userId = "
				+ userId
				+ " and shop_order.order_addTime between '"
				+ from
				+ "' and '" + to + "' group by order_sellerId ";
		rs = stmt.executeQuery(hotSeller);
		rs.next();
		result += rs.getString(1) + "," + rs.getInt(2) + ",";

		// 最喜欢的菜肴及购买次数
		String hotGoods = "select goods_name, count(*) as count  from shop_goods , shop_orderitem , shop_order  where  shop_order.order_no = shop_orderitem.item_orderNo and goods_id = item_goodsId and item_userId = "
				+ userId
				+ " and shop_order.order_addTime between '"
				+ from
				+ "' and '"
				+ to
				+ "' group by item_goodsId order by count desc";
		rs = stmt.executeQuery(hotGoods);
		System.out.println(hotGoods);
		rs.next();
		result += rs.getString(1) + "," + rs.getInt(2);
		rs.close();
		stmt.close();
		conn.close();
		return result;
	}

	// 得到一周之内未发货的订单
	public static List<OrderEntity> getNotDelivery(String userName)
			throws SQLException {
		List<OrderEntity> orderList = new ArrayList<OrderEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();

		UserEntity user = new UserEntity();
		user = UserService.getUser(userName);
		if (user.getId() == 0) {
			return null;
		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date d1 = new Date();
		Date d2 = new Date(d1.getTime() - 7 * 24 * 60 * 60 * 1000L);
		String now = sdf.format(d1);
		String last = sdf.format(d2);

		String sql = "select * from shop_order where  order_userId = "
				+ user.getId() + " and order_addTime between '" + last
				+ "' and '" + now + "' and order_state<3 group by order_no ";
		System.out.println(sql);

		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			// SimpleDateFormat sdf = new
			// SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			OrderEntity order = new OrderEntity();
			order.setId(rs.getInt("order_id"));
			order.setDetail(rs.getString("order_detail"));
			order.setNo(rs.getString("order_no"));
			order.setSellerId(rs.getInt("order_sellerId"));
			order.setTime(sdf.format(rs.getTimestamp("order_addTime")));
			order.setToAddr(rs.getString("order_state"));
			order.setToAddr(rs.getString("order_deliveryAddr"));
			order.setToUser(rs.getString("order_deliveryUser"));
			order.setToTime(rs.getString("order_deliveryTime"));
			order.setState(rs.getInt("order_state"));
			order.setCartList(CartService.selectRecent(userName,
					rs.getString("order_no")));
			orderList.add(order);
		}
		rs.close();
		stmt.close();
		conn.close();
		return orderList;
	}*/

	public static void main(String[] args) throws SQLException {
		// List<OrderEntity> cartList = getUserOrder(3, 0 + "", 30 + "");
		// for (OrderEntity order : cartList) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date now = new Date();
		Date last = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000L);
		String to = sdf.format(now);
		String from = sdf.format(last);
		//System.out.println("订单统计" + orderCount("liwentao", from, to));
		// // System.out.println("详情是" + order.getOrderItemList());
		// }

		// System.out.println(getNotDelivery("liwentao"));
	}
}